<?php

session_start();
require 'connection.php';
require('fpdf/fpdf.php');

$app = new \Slim\Slim();
$app->get('/operation/find_all/:type/:page', authorize('admin'), 'findAll');
//         /art_report/categor
$app->get('/operation/find/:type', authorize('admin'), 'find');
$app->get('/operation/dh_p', authorize('admin'), 'dh_purcharse');
$app->get('/operation/dh_s', authorize('admin'), 'dh_sale');
$app->get('/operation/search/:search', authorize('admin'), 'search');
$app->get('/operation/:id', authorize('admin'), 'read');
$app->post('/operation', authorize('admin'), 'add');
$app->post('/operation/report', authorize('admin'), 'report');
$app->put('/operation/:id', authorize('admin'), 'edit');
$app->delete('/operation/:id', authorize('admin'), 'delete');

$app->run();

function find($type) {
    $sql_list = "SELECT id, number, serie, date, client_id, total FROM operations  WHERE h.type = " . $type;
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_list);
        $stmt->execute();
        $rst_find = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($rst_find);
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
        return;
    }
}

function findAll($type, $page) {
    $page = ($page - 1) * 10;
    $sql_list = "SELECT h.id, h.number, h.serie, h.date, h.date_paid, o.name operator_id, h.total, h.subtotal, h.tax, h.type, h.document 
        FROM headers h 
        INNER JOIN operators o
        ON h.operator_id = o.id
        WHERE h.type = " . $type . " LIMIT " . $page . ", 10";
    $sql_count = "SELECT count(id) FROM headers WHERE type = " . $type;
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_list);
        $stmt->execute();
        $rst_find = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = $db->prepare($sql_count);
        $result->execute();
        $rst_count = $result->fetchColumn();
        $db = null;
        echo '{"result":' . json_encode($rst_find) . ', "count" :' . $rst_count . ' }';
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
        return;
    }
}

function search($search) {
    $sql_list = "SELECT id, name, code, address, type_operation FROM operations WHERE name LIKE '%" . $search . "%' ";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_list);
        $stmt->execute();
        $rst_find = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo '{"result":' . json_encode($rst_find) . ' , "count" :0}';
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
        return;
    }
}

function read($id) {
    $sql_header = "SELECT h.id, h.type, h.serie, h.number, h.date, h.operator_id, o.name, o.code, o.address 
        FROM headers h 
        INNER JOIN operators o
        ON h.operator_id = o.id
        WHERE h.id = :id;";
    $sql_detail = "SELECT d.id, d.header_id, d.article_id, d.price, d.quantity, a.name
        FROM details d
        INNER JOIN articles a
        ON d.article_id = a.id
        WHERE d.header_id = :id;";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_header);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $rst_header = $stmt->fetchObject();
        $stmt = $db->prepare($sql_detail);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $rst_detail = $stmt->fetchAll(PDO::FETCH_OBJ);

        $db = null;
        echo '{"header":' . json_encode($rst_header) . ',"details":' . json_encode($rst_detail) . '}';
        //echo json_encode($rst_detail);
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }
}

function add() {
    $sql_add_header = "INSERT INTO headers(type, serie, number, date, operator_id,total,tax,subtotal) 
        VALUES (:type, :serie, :number, :date, :operator_id,:total,:tax,:subtotal)";
    $sql_add_detail = "INSERT INTO details(header_id, article_id, price, quantity) VALUES (:header_id, :article_id, :price, :quantity)";

    $request = \Slim\Slim::getInstance()->request();
    $param = json_decode($request->getBody());
    try {
        $db = getConnection();
        $db->beginTransaction();
        $stmt = $db->prepare($sql_add_header);
        $stmt->bindParam("serie", $param->serie);
        $stmt->bindParam("number", $param->number);
        $stmt->bindParam("date", $param->date);
        $stmt->bindParam("type", $param->type);
        $stmt->bindParam("operator_id", $param->operator_id);
        $stmt->bindParam("total", $param->total);
        $stmt->bindParam("tax", $param->tax);
        $stmt->bindParam("subtotal", $param->subtotal);
        $stmt->execute();
        $header_id = $db->lastInsertId();
        foreach ($param->details as $detail) {
            $stmt = $db->prepare($sql_add_detail);
            $stmt->bindParam("header_id", $header_id);
            $stmt->bindParam("article_id", $detail->article_id);
            $stmt->bindParam("price", $detail->price);
            $stmt->bindParam("quantity", $detail->quantity);
            $stmt->execute();
        }
        $db->commit();
        echo json_encode($header_id);
    } catch (PDOException $e) {
        $db->rollBack();
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }
    $db = null;
}

function edit($id) {
    $sql_edit = "UPDATE operations SET name=:name WHERE id=:id";
    $request = \Slim\Slim::getInstance()->request();
    $body = $request->getBody();
    $param = json_decode($body);
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_edit);
        $stmt->bindParam("name", $param->name);

        $stmt->bindParam("id", $id);
        $stmt->execute();
        $db = null;
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }
}

function delete($id) {
    $sql_delete = "DELETE FROM headers WHERE id=:id";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_delete);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $db = null;
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
    }
}

function dh_purcharse() {
    //$sql_list = "SELECT sum(total) total,month(date) mes FROM backbone.headers where type = 1 group by month(date) order by month(date);";
    $sql_list = "SELECT sum(total) total,CASE period
WHEN 1 THEN 'Enero'
WHEN 2 THEN 'Febrero'
WHEN 3 THEN 'Marzo'
WHEN 4 THEN 'Abril'
WHEN 5 THEN 'Mayo'
WHEN 6 THEN 'Junio'
WHEN 7 THEN 'Julio'
WHEN 8 THEN 'Agosto'
WHEN 9 THEN 'Septiembre'
WHEN 10 THEN 'Octubre'
WHEN 11 THEN 'Noviembre'
WHEN 12 THEN 'Diciembre'
ELSE 'error' END

     mes FROM import_document where type = 1 group by period order by period";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_list);
        $stmt->execute();
        $rst_find = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($rst_find);
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
        return;
    }
}

function dh_sale() {
    //$sql_list = "SELECT sum(total) total,month(date) mes FROM backbone.headers where type = 2 group by month(date) order by month(date);";
    $sql_list = "SELECT sum(total) total,period mes FROM import_document where type = 2 group by period order by period";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql_list);
        $stmt->execute();
        $rst_find = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($rst_find);
    } catch (PDOException $e) {
        echo '{"error":{"text":' . $e->getMessage() . '}}';
        return;
    }
}

function report() {
    $pdf = new FPDF();
    $pdf->AddPage();
    $pdf->SetFont('Arial', 'B', 16);
    $pdf->Cell(40, 10, utf8_decode('¡Mi primera página pdf con FPDF!'));
    //$pdf->Output();
    $pdf->Output("Reporte.pdf", "D"); //
}

?>